參考James Fu 的技術學習之路網址:
https://dotblogs.com.tw/jamesfu/2015/05/17/sp_largedelete
原始語法如下:
CREATE PROCEDURE [dbo].[sp_LargeDelete]
@TableName sysname,
@MaxRows int = 100000,
@Filter nvarchar(512) = ''
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL nvarchar(512)
BEGIN TRY
IF ( @Filter = '' OR @Filter is null )
BEGIN
SET @SQL = 'TRUNCATE TABLE '+@TableName ;
exec sp_executesql @SQL
END
ELSE
BEGIN
DECLARE @Count INT = -1
SET @SQL = 'DELETE TOP ('+CAST(@MaxRows AS varchar) + ') FROM ' + @TableName + ' WHERE ' + @Filter + ' OPTION ( MAXDOP 1 )' ;
WHILE @Count <> 0
BEGIN
BEGIN TRAN
exec sp_executesql @SQL
SET @Count = @@ROWCOUNT
COMMIT
END
END
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
END